package zy.dao.money.expense.impl;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.money.expense.ExpenseDAO;
import zy.dto.money.expense.ExpenseReportDepartmentDto;
import zy.dto.money.expense.ExpenseReportDto;
import zy.dto.money.expense.ExpenseReportMonthDto;
import zy.entity.money.expense.T_Money_Expense;
import zy.entity.money.expense.T_Money_ExpenseList;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class ExpenseDAOImpl extends BaseDaoImpl implements ExpenseDAO{
	@Override
	public Integer count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object ep_ar_state = params.get("ep_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ep_shop_code = params.get("ep_shop_code");
		Object ep_ba_code = params.get("ep_ba_code");
		Object ep_manager = params.get("ep_manager");
		Object ep_number = params.get("ep_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_money_expense t");
		sql.append(" JOIN t_base_shop sp ON sp_code = ep_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(ep_shop_code != null && !"".equals(ep_shop_code)){
				sql.append(" AND ep_shop_code = :ep_shop_code");
			}
		}else{//加盟店
			sql.append(" JOIN common_type on ty_id=sp_shop_type");
			sql.append(" WHERE 1 = 1");
			sql.append(" AND ep_shop_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(ep_ar_state)) {
			sql.append(" AND ep_ar_state = :ep_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ep_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ep_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(ep_ba_code)) {
			sql.append(" AND ep_ba_code = :ep_ba_code ");
		}
		if (StringUtil.isNotEmpty(ep_manager)) {
			sql.append(" AND ep_manager = :ep_manager ");
		}
		if (StringUtil.isNotEmpty(ep_number)) {
			sql.append(" AND INSTR(ep_number,:ep_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Money_Expense> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object ep_ar_state = params.get("ep_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ep_shop_code = params.get("ep_shop_code");
		Object ep_ba_code = params.get("ep_ba_code");
		Object ep_manager = params.get("ep_manager");
		Object ep_number = params.get("ep_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ep_id,ep_number,ep_shop_code,ep_date,ep_maker,ep_manager,ep_ba_code,ep_dm_code,ep_money,ep_us_id,ep_sysdate,");
		sql.append(" ep_ar_state,ep_ar_date,ep_share,ep_remark,t.companyid,sp.sp_name as shop_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba.ba_code=t.ep_ba_code AND ba.companyid=t.companyid LIMIT 1) AS ba_name, ");
		sql.append(" (SELECT dm_name FROM t_base_department dm WHERE dm_code = ep_dm_code AND dm.companyid = t.companyid LIMIT 1) AS dm_name");
		sql.append(" FROM t_money_expense t");
		sql.append(" JOIN t_base_shop sp ON sp_code = ep_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(ep_shop_code != null && !"".equals(ep_shop_code)){
				sql.append(" AND ep_shop_code = :ep_shop_code");
			}
		}else{//加盟店
			sql.append(" JOIN common_type on ty_id=sp_shop_type");
			sql.append(" WHERE 1 = 1");
			sql.append(" AND ep_shop_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(ep_ar_state)) {
			sql.append(" AND ep_ar_state = :ep_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ep_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ep_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(ep_ba_code)) {
			sql.append(" AND ep_ba_code = :ep_ba_code ");
		}
		if (StringUtil.isNotEmpty(ep_manager)) {
			sql.append(" AND ep_manager = :ep_manager ");
		}
		if (StringUtil.isNotEmpty(ep_number)) {
			sql.append(" AND INSTR(ep_number,:ep_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ep_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Money_Expense.class));
	}

	@Override
	public T_Money_Expense load(Integer ep_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ep_id,ep_number,ep_shop_code,ep_date,ep_maker,ep_manager,ep_ba_code,ep_dm_code,ep_money,ep_us_id,ep_sysdate,");
		sql.append(" ep_ar_state,ep_ar_date,ep_share,ep_remark,t.companyid,sp.sp_name as shop_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba.ba_code=t.ep_ba_code AND ba.companyid=t.companyid LIMIT 1) AS ba_name, ");
		sql.append(" (SELECT dm_name FROM t_base_department dm WHERE dm_code = ep_dm_code AND dm.companyid = t.companyid LIMIT 1) AS dm_name");
		sql.append(" FROM t_money_expense t");
		sql.append(" JOIN t_base_shop sp ON sp_code = ep_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE ep_id = :ep_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ep_id", ep_id),
					new BeanPropertyRowMapper<>(T_Money_Expense.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Money_Expense load(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ep_id,ep_number,ep_shop_code,ep_date,ep_maker,ep_manager,ep_ba_code,ep_dm_code,ep_money,ep_us_id,ep_sysdate,");
		sql.append(" ep_ar_state,ep_ar_date,ep_share,ep_remark,t.companyid,sp.sp_name as shop_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba.ba_code=t.ep_ba_code AND ba.companyid=t.companyid LIMIT 1) AS ba_name, ");
		sql.append(" (SELECT dm_name FROM t_base_department dm WHERE dm_code = ep_dm_code AND dm.companyid = t.companyid LIMIT 1) AS dm_name");
		sql.append(" FROM t_money_expense t");
		sql.append(" JOIN t_base_shop sp ON sp_code = ep_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE ep_number = :ep_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("ep_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Money_Expense.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Money_Expense check(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ep_id,ep_number,ep_shop_code,ep_date,ep_maker,ep_manager,ep_ba_code,ep_dm_code,ep_money,ep_us_id,ep_sysdate,");
		sql.append(" ep_ar_state,ep_ar_date,ep_share,ep_remark,companyid");
		sql.append(" FROM t_money_expense t");
		sql.append(" WHERE ep_number = :ep_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("ep_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Money_Expense.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<T_Money_Expense> check(List<String> numbers,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ep_id,ep_number,ep_shop_code,ep_date,ep_maker,ep_manager,ep_ba_code,ep_dm_code,ep_money,ep_us_id,ep_sysdate,");
		sql.append(" ep_ar_state,ep_ar_date,ep_share,ep_remark,companyid");
		sql.append(" FROM t_money_expense t");
		sql.append(" WHERE ep_number IN (:numbers)");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("numbers", numbers).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Money_Expense.class));
	}

	@Override
	public List<T_Money_ExpenseList> temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT epl_id,epl_mp_code,epl_money,epl_remark,epl_sharemonth,epl_us_id,companyid,");
		sql.append(" (SELECT pp_name FROM t_money_property pp WHERE pp_type = 0 AND pp_code = epl_mp_code AND pp.companyid = t.companyid LIMIT 1) AS mp_name");
		sql.append(" FROM t_money_expenselist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND epl_us_id = :epl_us_id");
		sql.append(" AND companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY epl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Money_ExpenseList.class));
	}

	@Override
	public List<String> temp_check(Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT epl_mp_code");
		sql.append(" FROM t_money_expenselist_temp t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND epl_us_id = :epl_us_id");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("epl_us_id", us_id).addValue("companyid", companyid), String.class);
	}
	
	@Override
	public void temp_save(List<T_Money_ExpenseList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_money_expenselist_temp");
		sql.append(" (epl_mp_code,epl_money,epl_remark,epl_sharemonth,epl_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:epl_mp_code,:epl_money,:epl_remark,:epl_sharemonth,:epl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}

	@Override
	public void temp_updateMoney(T_Money_ExpenseList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_money_expenselist_temp");
		sql.append(" SET epl_money = :epl_money");
		sql.append(" WHERE 1=1");
		sql.append(" AND epl_id = :epl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateSharemonth(T_Money_ExpenseList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_money_expenselist_temp");
		sql.append(" SET epl_sharemonth = :epl_sharemonth");
		sql.append(" WHERE 1=1");
		sql.append(" AND epl_id = :epl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_updateRemark(T_Money_ExpenseList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_money_expenselist_temp");
		sql.append(" SET epl_remark = :epl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND epl_id = :epl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_del(Integer epl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_money_expenselist_temp");
		sql.append(" WHERE epl_id=:epl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("epl_id", epl_id));
	}

	@Override
	public void temp_clear(Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_money_expenselist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND epl_us_id = :epl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("epl_us_id", us_id).addValue("companyid", companyid));
	}

	@Override
	public List<T_Money_ExpenseList> detail_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT epl_id,epl_number,epl_mp_code,epl_money,epl_remark,epl_sharedate,companyid,");
		sql.append(" (SELECT pp_name FROM t_money_property pp WHERE pp_type = 0 AND pp_code = epl_mp_code AND pp.companyid = t.companyid LIMIT 1) AS mp_name");
		sql.append(" FROM t_money_expenselist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND epl_number = :epl_number");
		sql.append(" AND companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY epl_id ASC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Money_ExpenseList.class));
	}
	
	@Override
	public List<T_Money_ExpenseList> detail_list_forsavetemp(String ep_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT epl_number,epl_mp_code,SUM(epl_money) AS epl_money,epl_remark,COUNT(1) AS epl_sharemonth,companyid");
		sql.append(" FROM t_money_expenselist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND epl_number = :epl_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY epl_mp_code");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("epl_number", ep_number).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Money_ExpenseList.class));
	}
	
	@Override
	public void save(T_Money_Expense expense, List<T_Money_ExpenseList> details) {
		String prefix = CommonUtil.NUMBER_PREFIX_MONEY_EXPENSE + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(ep_number))) AS new_number");
		sql.append(" FROM t_money_expense");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(ep_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", expense.getCompanyid()), String.class);
		expense.setEp_number(new_number);
		sql.setLength(0);
		sql.append(" INSERT INTO t_money_expense");
		sql.append(" (ep_number,ep_shop_code,ep_date,ep_maker,ep_manager,ep_ba_code,ep_dm_code,ep_money,ep_us_id,ep_sysdate,");
		sql.append(" ep_ar_state,ep_ar_date,ep_share,ep_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ep_number,:ep_shop_code,:ep_date,:ep_maker,:ep_manager,:ep_ba_code,:ep_dm_code,:ep_money,:ep_us_id,:ep_sysdate,");
		sql.append(" :ep_ar_state,:ep_ar_date,:ep_share,:ep_remark,:companyid)");
		
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(expense),holder);
		expense.setEp_id(holder.getKey().intValue());
		for(T_Money_ExpenseList item:details){
			item.setEpl_number(expense.getEp_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_money_expenselist");
		sql.append(" (epl_number,epl_mp_code,epl_money,epl_remark,epl_sharedate,companyid)");
		sql.append(" VALUES");
		sql.append(" (:epl_number,:epl_mp_code,:epl_money,:epl_remark,:epl_sharedate,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void update(T_Money_Expense expense, List<T_Money_ExpenseList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_money_expense");
		sql.append(" SET ep_date=:ep_date");
		sql.append(" ,ep_shop_code=:ep_shop_code");
		sql.append(" ,ep_maker=:ep_maker");
		sql.append(" ,ep_manager=:ep_manager");
		sql.append(" ,ep_dm_code=:ep_dm_code");
		sql.append(" ,ep_money=:ep_money");
		sql.append(" ,ep_remark=:ep_remark");
		sql.append(" ,ep_ar_state=:ep_ar_state");
		sql.append(" ,ep_ar_date=:ep_ar_date");
		sql.append(" ,ep_share=:ep_share");
		sql.append(" ,ep_us_id=:ep_us_id");
		sql.append(" WHERE ep_id=:ep_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(expense));
		for(T_Money_ExpenseList item:details){
			item.setEpl_number(expense.getEp_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_money_expenselist");
		sql.append(" (epl_number,epl_mp_code,epl_money,epl_remark,epl_sharedate,companyid)");
		sql.append(" VALUES");
		sql.append(" (:epl_number,:epl_mp_code,:epl_money,:epl_remark,:epl_sharedate,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void updateApprove(T_Money_Expense expense) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_money_expense");
		sql.append(" SET ep_ar_state=:ep_ar_state");
		sql.append(" ,ep_ar_date = :ep_ar_date");
		sql.append(" WHERE ep_id=:ep_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(expense));
	}
	
	@Override
	public void updateApprove(List<T_Money_Expense> expenses) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_money_expense");
		sql.append(" SET ep_ar_state=:ep_ar_state");
		sql.append(" ,ep_ar_date = :ep_ar_date");
		sql.append(" WHERE ep_id=:ep_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(expenses.toArray()));
	}
	
	@Override
	public void del(List<String> numbers, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_money_expense");
		sql.append(" WHERE ep_number IN (:numbers)");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("numbers", numbers).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_money_expenselist");
		sql.append(" WHERE epl_number IN (:numbers)");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("numbers", numbers).addValue("companyid", companyid));
	}
	
	@Override
	public void deleteList(String ep_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_money_expenselist");
		sql.append(" WHERE epl_number=:epl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("epl_number", ep_number).addValue("companyid", companyid));
	}

	private String getReportSQL(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		if(StringUtil.isNotEmpty(params.get("month"))){
			sql.append(" AND MONTH(epl_sharedate) = :month ");
		}
		if(StringUtil.isNotEmpty(params.get("epl_mp_code"))){
			sql.append(" AND epl_mp_code = :epl_mp_code ");
		}
		if(StringUtil.isNotEmpty(params.get("ep_shop_code"))){
			sql.append(" AND ep_shop_code = :ep_shop_code ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND epl_sharedate >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND epl_sharedate <= :enddate ");
		}
		sql.append(" AND ep_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public List<ExpenseReportDto> listReport(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		if("shop".equals(type)){
			sql.append(" ep_shop_code AS code,sp_name AS name,");
		}else if("property".equals(type)){
			sql.append(" epl_mp_code AS code,");
			sql.append(" (SELECT pp_name FROM t_money_property pp WHERE pp_type = 0 AND pp_code = epl_mp_code AND pp.companyid = t.companyid LIMIT 1) AS name,");
		}else if("month".equals(type)){
			sql.append(" MONTH(epl_sharedate) AS code,CONCAT(MONTH(epl_sharedate),'月份') AS name,");
		}
		sql.append(" SUM(epl_money) AS money");
		sql.append(" FROM t_money_expense t");
		sql.append(" JOIN t_money_expenselist epl ON epl_number = ep_number AND epl.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = ep_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//加盟店、自营店、合伙店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND ep_shop_code = :shop_code");
		}
		sql.append(getReportSQL(params));
		if("shop".equals(type)){
			sql.append(" GROUP BY ep_shop_code");
		}else if("property".equals(type)){
			sql.append(" GROUP BY epl_mp_code");
		}else if("month".equals(type)){
			sql.append(" GROUP BY MONTH(epl_sharedate)");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(ExpenseReportDto.class));
	}
	
	@Override
	public List<T_Money_ExpenseList> listReportDetail(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" epl_id,epl_sharedate,epl_mp_code,epl_money,epl_remark,");
		sql.append(" (SELECT pp_name FROM t_money_property pp WHERE pp_type = 0 AND pp_code = epl_mp_code AND pp.companyid = t.companyid LIMIT 1) AS mp_name");
		sql.append(" FROM t_money_expense t");
		sql.append(" JOIN t_money_expenselist epl ON epl_number = ep_number AND epl.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = ep_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//加盟店、自营店、合伙店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND ep_shop_code = :shop_code");
		}
		sql.append(getReportSQL(params));
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY epl_id ASC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Money_ExpenseList.class));
	}

	@Override
	public Map<String, ExpenseReportMonthDto> expense_head(Map<String, Object> params) {
		final Map<String, ExpenseReportMonthDto> resultMap = new HashMap<String, ExpenseReportMonthDto>();
		StringBuffer sql = new StringBuffer();
		
		//业务收入--配货金额
		sql.append(" SELECT MONTH(at_date) AS month,SUM(at_sendmoney) AS money ");
		sql.append(" FROM t_sort_allot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = at_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND (sp_shop_type = "+CommonUtil.TWO);
		sql.append(" OR (sp_upcode = :shop_code AND sp_shop_type IN("+CommonUtil.FOUR+","+CommonUtil.FIVE+"))");
		sql.append(" )");
		sql.append(" AND at_date >= :begindate ");
		sql.append(" AND at_date <= :enddate ");
		sql.append(" AND at_ar_state IN (3,4,5)");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(at_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new ExpenseReportMonthDto());
				}
				resultMap.get(month).setBusiness_income(rs.getDouble("money"));
				return null;
			};
		});
		//业务收入--批发金额
		sql.setLength(0);
		sql.append(" SELECT MONTH(se_make_date) AS month,SUM(se_money) AS money");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND se_ar_state = 1");
		sql.append(" AND se_make_date >= :begindate ");
		sql.append(" AND se_make_date <= :enddate ");
		sql.append(" AND ci_sp_code = :shop_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(se_make_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new ExpenseReportMonthDto());
				}
				resultMap.get(month).setBusiness_income(rs.getDouble("money")+resultMap.get(month).getBusiness_income());
				return null;
			};
		});
		//业务成本--采购金额
		sql.setLength(0);
		sql.append(" SELECT MONTH(et_make_date) AS month,SUM(et_money) AS money");
		sql.append(" FROM t_buy_enter t");
		sql.append(" WHERE 1=1");
		sql.append(" AND et_make_date >= :begindate ");
		sql.append(" AND et_make_date <= :enddate ");
		sql.append(" AND et_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(et_make_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new ExpenseReportMonthDto());
				}
				resultMap.get(month).setBusiness_cost(rs.getDouble("money"));
				return null;
			};
		});
		
		//费用
		sql.setLength(0);
		sql.append(" SELECT MONTH(epl_sharedate) AS month,SUM(epl_money) AS money");
		sql.append(" FROM t_money_expense t");
		sql.append(" JOIN t_money_expenselist epl ON epl_number = ep_number AND epl.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = ep_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND epl_sharedate >= :begindate ");
		sql.append(" AND epl_sharedate <= :enddate ");
		sql.append(" AND ep_ar_state = 1");
		sql.append(" AND ep_shop_code = :shop_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(epl_sharedate)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new ExpenseReportMonthDto());
				}
				resultMap.get(month).setExpense(rs.getDouble("money"));
				return null;
			};
		});
		return resultMap;
	}
	@Override
	public Map<String, ExpenseReportMonthDto> expense_shop(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		final Map<String, ExpenseReportMonthDto> resultMap = new HashMap<String, ExpenseReportMonthDto>();
		StringBuffer sql = new StringBuffer();
		//业务收入、业务成本
		sql.append(" SELECT MONTH(sh_date) AS month,SUM(sh_money) AS money,SUM(sh_cost_money) AS costMoney");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1 = 1");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			String shopCode = StringUtil.trimString(params.get("shopCode"));
			if(StringUtil.isNotEmpty(shopCode)){
				params.put("shop_codes", Arrays.asList(shopCode.toString().split(",")));
				sql.append(" AND sh_shop_code IN(:shop_codes)");
			}
		}else{//自营、加盟、合伙
			sql.append(" AND sh_shop_code = :shop_code");
		}
		sql.append(" AND sh_date >= :begindate ");
		sql.append(" AND sh_date <= :enddate ");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(sh_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new ExpenseReportMonthDto());
				}
				resultMap.get(month).setBusiness_income(rs.getDouble("money"));
				resultMap.get(month).setBusiness_cost(rs.getDouble("costMoney"));
				return null;
			};
		});
		//费用
		sql.setLength(0);
		sql.append(" SELECT MONTH(epl_sharedate) AS month,SUM(epl_money) AS money");
		sql.append(" FROM t_money_expense t");
		sql.append(" JOIN t_money_expenselist epl ON epl_number = ep_number AND epl.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = ep_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND epl_sharedate >= :begindate ");
		sql.append(" AND epl_sharedate <= :enddate ");
		sql.append(" AND ep_ar_state = 1");
		sql.append(" AND ep_shop_code = :shop_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY MONTH(epl_sharedate)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String month = rs.getString("month");
				if(!resultMap.containsKey(month)){
					resultMap.put(month, new ExpenseReportMonthDto());
				}
				resultMap.get(month).setExpense(rs.getDouble("money"));
				return null;
			};
		});
		return resultMap;
	}
	@Override
	public List<ExpenseReportDepartmentDto> expense_department(Map<String,Object> params){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT epl_mp_code AS mp_code,");
		sql.append(" (SELECT pp_name FROM t_money_property pp WHERE pp_code = epl_mp_code AND pp_type = 0 AND pp.companyid = :companyid LIMIT 1) AS mp_name,");
		sql.append(" GROUP_CONCAT(CONCAT(ep_dm_code,':',money)) AS depart_money");
		sql.append(" FROM(");
		sql.append(" SELECT epl_mp_code,ep_dm_code,SUM(epl_money) AS money");
		sql.append(" FROM t_money_expense t");
		sql.append(" JOIN t_money_expenselist epl ON ep_number = epl_number AND t.companyid = epl.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND epl_sharedate >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND epl_sharedate <= :enddate ");
		}
		String mp_code = StringUtil.trimString(params.get("mp_code"));
		String dm_code = StringUtil.trimString(params.get("dm_code"));
		if(StringUtil.isNotEmpty(mp_code)){
			params.put("mp_codes", Arrays.asList(mp_code.toString().split(",")));
			sql.append(" AND epl_mp_code IN(:mp_codes)");
		}
		if(StringUtil.isNotEmpty(dm_code)){
			params.put("dm_codes", Arrays.asList(dm_code.toString().split(",")));
			sql.append(" AND ep_dm_code IN(:dm_codes)");
		}
		sql.append(" AND ep_ar_state  = 1");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY epl_mp_code,ep_dm_code");
		sql.append(" )temp");
		sql.append(" GROUP BY epl_mp_code");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(ExpenseReportDepartmentDto.class));
	}

	@Override
	public List<T_Money_Expense> monthMoney(Map<String, Object> paramMap) {
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		Object date = paramMap.get("date");
		Object sh_shop_code = paramMap.get("sh_shop_code");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT DAY(ep_date) ep_date,SUM(ep_money) ep_money");
		sql.append(" FROM t_money_expense t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.ep_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(sh_shop_code)){
			paramMap.put("shop_codes", StringUtil.parseList(StringUtil.trimString(sh_shop_code)));
			sql.append(" AND sp_code IN (:shop_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code=:shop_code");
		}
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(date)){
			sql.append(" AND INSTR(ep_date,:date)>0");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY DAY(ep_date)");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Money_Expense.class));
	}
	
}
